{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "59195006",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from sqlalchemy import create_engine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "3c4c46ab",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 数据库地址：数据库放在上一级目录下\n",
    "db_path = os.path.join(os.path.dirname(os.getcwd()), \"data.db\")\n",
    "engine_path = \"sqlite:///\" + db_path\n",
    "# 创建数据库引擎\n",
    "engine = create_engine(engine_path)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eb0ea05d",
   "metadata": {},
   "source": [
    "## 生成excel数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "d593b110",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = \"\"\"\n",
    "select \n",
    "*\n",
    "from\n",
    "users\n",
    "\"\"\"\n",
    "\n",
    "df = pd.read_sql(sql, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "bd214aa3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>username</th>\n",
       "      <th>age</th>\n",
       "      <th>mobile</th>\n",
       "      <th>idcard</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>IFIC6l</td>\n",
       "      <td>47</td>\n",
       "      <td>16019279145</td>\n",
       "      <td>732963960433655083</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>kk2FTQ</td>\n",
       "      <td>34</td>\n",
       "      <td>18110974499</td>\n",
       "      <td>012265241947100277</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>ecofLg</td>\n",
       "      <td>34</td>\n",
       "      <td>30923348203</td>\n",
       "      <td>175614982440554168</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>mG1Tu5</td>\n",
       "      <td>43</td>\n",
       "      <td>16959949516</td>\n",
       "      <td>091640000257768986</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>hp33mF</td>\n",
       "      <td>46</td>\n",
       "      <td>84967379619</td>\n",
       "      <td>889404031776530996</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   user_id username  age       mobile              idcard\n",
       "0        1   IFIC6l   47  16019279145  732963960433655083\n",
       "1        2   kk2FTQ   34  18110974499  012265241947100277\n",
       "2        3   ecofLg   34  30923348203  175614982440554168\n",
       "3        4   mG1Tu5   43  16959949516  091640000257768986\n",
       "4        5   hp33mF   46  84967379619  889404031776530996"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "a3b54f4e",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_excel(\"./md5加密数据.xlsx\",index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "151bc79a",
   "metadata": {},
   "outputs": [],
   "source": [
    "md5_excel_df = pd.read_excel(\"./md5加密数据.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "b741e214",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>username</th>\n",
       "      <th>age</th>\n",
       "      <th>mobile</th>\n",
       "      <th>idcard</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>IFIC6l</td>\n",
       "      <td>47</td>\n",
       "      <td>16019279145</td>\n",
       "      <td>732963960433655083</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>kk2FTQ</td>\n",
       "      <td>34</td>\n",
       "      <td>18110974499</td>\n",
       "      <td>12265241947100277</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>ecofLg</td>\n",
       "      <td>34</td>\n",
       "      <td>30923348203</td>\n",
       "      <td>175614982440554168</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>mG1Tu5</td>\n",
       "      <td>43</td>\n",
       "      <td>16959949516</td>\n",
       "      <td>91640000257768986</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>hp33mF</td>\n",
       "      <td>46</td>\n",
       "      <td>84967379619</td>\n",
       "      <td>889404031776530996</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   user_id username  age       mobile              idcard\n",
       "0        1   IFIC6l   47  16019279145  732963960433655083\n",
       "1        2   kk2FTQ   34  18110974499   12265241947100277\n",
       "2        3   ecofLg   34  30923348203  175614982440554168\n",
       "3        4   mG1Tu5   43  16959949516   91640000257768986\n",
       "4        5   hp33mF   46  84967379619  889404031776530996"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "md5_excel_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "134e55db",
   "metadata": {},
   "source": [
    "## md5加密函数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "87211b28",
   "metadata": {},
   "outputs": [],
   "source": [
    "def encryption_str(string, encry_model=\"md5_32\", encry_style=True):\n",
    "    import hashlib\n",
    "    # 加密为 utf-8 编码\n",
    "    utf_8_str = str(string).encode(\"utf8\")\n",
    "    # 函数字典\n",
    "    param_dict = {\n",
    "        \"md5_32\": hashlib.md5(utf_8_str),\n",
    "        \"md5_16\": hashlib.md5(utf_8_str),\n",
    "        \"sha1\": hashlib.sha1(utf_8_str),\n",
    "        \"sha224\": hashlib.sha224(utf_8_str),\n",
    "        \"sha256\": hashlib.sha256(utf_8_str),\n",
    "        \"sha512\": hashlib.sha512(utf_8_str)\n",
    "    }\n",
    "    encry_result = param_dict[encry_model].hexdigest()\n",
    "    if encry_model == 'md5_16':\n",
    "        encry_result = encry_result[8:-8]\n",
    "    # 返回结果\n",
    "    return encry_result if encry_style == \"小写\" else encry_result.upper()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dfee2ef5",
   "metadata": {},
   "source": [
    "## 对excel数据进行加密"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "9e7ed7db",
   "metadata": {},
   "outputs": [],
   "source": [
    "md5_excel_df[\"mobile_md5\"] = md5_excel_df[\"mobile\"].map(lambda x:encryption_str(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "0f04a6b8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>username</th>\n",
       "      <th>age</th>\n",
       "      <th>mobile</th>\n",
       "      <th>idcard</th>\n",
       "      <th>mobile_md5</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>IFIC6l</td>\n",
       "      <td>47</td>\n",
       "      <td>16019279145</td>\n",
       "      <td>732963960433655083</td>\n",
       "      <td>44B72BB81FDE743458D162C44966979D</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>kk2FTQ</td>\n",
       "      <td>34</td>\n",
       "      <td>18110974499</td>\n",
       "      <td>12265241947100277</td>\n",
       "      <td>9035E59C7F2F7BADC0BCD8E8B192642D</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>ecofLg</td>\n",
       "      <td>34</td>\n",
       "      <td>30923348203</td>\n",
       "      <td>175614982440554168</td>\n",
       "      <td>D699DA6638BA30869F80D4F479B6818F</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>mG1Tu5</td>\n",
       "      <td>43</td>\n",
       "      <td>16959949516</td>\n",
       "      <td>91640000257768986</td>\n",
       "      <td>047168E9AA21966AFD6C7E8064FF6F1E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>hp33mF</td>\n",
       "      <td>46</td>\n",
       "      <td>84967379619</td>\n",
       "      <td>889404031776530996</td>\n",
       "      <td>C7BE6B1241D046E53EAAD8CE84462F94</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   user_id username  age       mobile              idcard  \\\n",
       "0        1   IFIC6l   47  16019279145  732963960433655083   \n",
       "1        2   kk2FTQ   34  18110974499   12265241947100277   \n",
       "2        3   ecofLg   34  30923348203  175614982440554168   \n",
       "3        4   mG1Tu5   43  16959949516   91640000257768986   \n",
       "4        5   hp33mF   46  84967379619  889404031776530996   \n",
       "\n",
       "                         mobile_md5  \n",
       "0  44B72BB81FDE743458D162C44966979D  \n",
       "1  9035E59C7F2F7BADC0BCD8E8B192642D  \n",
       "2  D699DA6638BA30869F80D4F479B6818F  \n",
       "3  047168E9AA21966AFD6C7E8064FF6F1E  \n",
       "4  C7BE6B1241D046E53EAAD8CE84462F94  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "md5_excel_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "1a232d18",
   "metadata": {},
   "outputs": [],
   "source": [
    "md5_excel_df[\"idcard_md5\"] = md5_excel_df[\"idcard\"].map(lambda x:encryption_str(x,\"md5_16\",\"大写\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "6d96c45c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>username</th>\n",
       "      <th>age</th>\n",
       "      <th>mobile</th>\n",
       "      <th>idcard</th>\n",
       "      <th>mobile_md5</th>\n",
       "      <th>idcard_md5</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>IFIC6l</td>\n",
       "      <td>47</td>\n",
       "      <td>16019279145</td>\n",
       "      <td>732963960433655083</td>\n",
       "      <td>44B72BB81FDE743458D162C44966979D</td>\n",
       "      <td>41FD551BB1E4A2D6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>kk2FTQ</td>\n",
       "      <td>34</td>\n",
       "      <td>18110974499</td>\n",
       "      <td>12265241947100277</td>\n",
       "      <td>9035E59C7F2F7BADC0BCD8E8B192642D</td>\n",
       "      <td>C3595E99A8045875</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>ecofLg</td>\n",
       "      <td>34</td>\n",
       "      <td>30923348203</td>\n",
       "      <td>175614982440554168</td>\n",
       "      <td>D699DA6638BA30869F80D4F479B6818F</td>\n",
       "      <td>237E29A14CFFD9CB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>mG1Tu5</td>\n",
       "      <td>43</td>\n",
       "      <td>16959949516</td>\n",
       "      <td>91640000257768986</td>\n",
       "      <td>047168E9AA21966AFD6C7E8064FF6F1E</td>\n",
       "      <td>4801A3782705F740</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>hp33mF</td>\n",
       "      <td>46</td>\n",
       "      <td>84967379619</td>\n",
       "      <td>889404031776530996</td>\n",
       "      <td>C7BE6B1241D046E53EAAD8CE84462F94</td>\n",
       "      <td>173F96E8F163C652</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   user_id username  age       mobile              idcard  \\\n",
       "0        1   IFIC6l   47  16019279145  732963960433655083   \n",
       "1        2   kk2FTQ   34  18110974499   12265241947100277   \n",
       "2        3   ecofLg   34  30923348203  175614982440554168   \n",
       "3        4   mG1Tu5   43  16959949516   91640000257768986   \n",
       "4        5   hp33mF   46  84967379619  889404031776530996   \n",
       "\n",
       "                         mobile_md5        idcard_md5  \n",
       "0  44B72BB81FDE743458D162C44966979D  41FD551BB1E4A2D6  \n",
       "1  9035E59C7F2F7BADC0BCD8E8B192642D  C3595E99A8045875  \n",
       "2  D699DA6638BA30869F80D4F479B6818F  237E29A14CFFD9CB  \n",
       "3  047168E9AA21966AFD6C7E8064FF6F1E  4801A3782705F740  \n",
       "4  C7BE6B1241D046E53EAAD8CE84462F94  173F96E8F163C652  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "md5_excel_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "54905387",
   "metadata": {},
   "outputs": [],
   "source": [
    "md5_excel_df.to_excel(\"./md5加密数据已完成.xlsx\",index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "73ff6ddd",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5dd3958b",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
